package db.history;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.Database;
import dbservice.history.经营情况DBService;
import po.AccountBookPo;
import po.Po;
import po.经营情况po;

public class 经营情况DB implements 经营情况DBService{
	private AccountBookPo ab;
	
	public 经营情况DB(AccountBookPo ab) throws SQLException{
		String creatTable="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_经营情况` (    `日期` BIGINT(0) NOT NULL,  `销售收入` BIGINT(0) NOT NULL,  `商品报溢收入` BIGINT(0) NOT NULL,  `成本调价收入` BIGINT(0) NOT NULL,  `进货退货差价收入` BIGINT(0) NOT NULL,  `代金券与实际收款差额收入` BIGINT(0) NOT NULL,  `销售成本` BIGINT(0) NOT NULL,  `商品报损支出` BIGINT(0) NOT NULL,  `商品赠出支出` BIGINT(0) NOT NULL,`折让` BIGINT(0) NOT NULL,  PRIMARY KEY (`日期`));";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		s.close();
		this.ab=ab;
	} 
	
	public List<经营情况po> get经营情况(){
		List<经营情况po> result=new ArrayList<经营情况po>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r= s.executeQuery("select * from "+ab.getName()+"_经营情况");
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}
	
	public List<经营情况po> get经营情况(经营情况po from){
		List<经营情况po> result=new ArrayList<经营情况po>();		
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r= s.executeQuery("select * from "+ab.getName()+"_经营情况 where 日期>="+from.get日期());
			readResultSet(r,result);
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
		
	}
	
	private void readResultSet(ResultSet r,List<经营情况po> result) throws SQLException{
		while(r.next()){
			经营情况po temp=new 经营情况po();
			temp.set日期(r.getLong(1));
			temp.set销售收入(r.getLong(2));
			temp.set商品报溢收入(r.getLong(3));
			temp.set成本调价收入(r.getLong(4));
			temp.set进货退货差价收入(r.getLong(5));
			temp.set代金券与实际收款差额收入(r.getLong(6));
			temp.set销售成本(r.getLong(7));
			temp.set商品报损支出(r.getLong(8));
			temp.set商品赠出支出(r.getLong(9));
			temp.set折让(r.getLong(10));
			result.add(temp);
		}
	}

	@Override
	public Po add销售收入(long in) {
		return add("销售收入",in);
	}

	@Override
	public Po add商品报溢收入(long in) {
		return add("商品报溢收入",in);
	}

	@Override
	public Po add成本调价收入(long in) {
		return add("成本调价收入",in);
	}

	@Override
	public Po add进货退货差价收入(long in) {
		return add("进货退货差价收入",in);
	}

	@Override
	public Po add代金券与实际收款差额收入(long in) {
		return add("代金券与实际收款差额收入",in);
	}

	@Override
	public Po add销售成本(long in) {
		return add("销售成本",in);
	}

	@Override
	public Po add商品报损支出(long in) {
		return add("商品报损支出",in);
	}

	@Override
	public Po add商品赠出支出(long in) {
		return add("商品赠出支出",in);
	}
	
	@Override
	public Po add折让(long in) {
		return add("折让",in);
	}
	
	private void add经营情况(Date date){
		经营情况po po=new 经营情况po();
		po.set日期(date.getTime());
		add(po);
	}
	
	private void add(经营情况po po){
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute("insert into "+ab.getName()+"_经营情况 values('"+po.get日期()+"','"+po.get销售收入()+"','"+po.get商品报溢收入()+"','"+po.get成本调价收入()+"','"+po.get进货退货差价收入()+"','"+po.get代金券与实际收款差额收入()+"','"+po.get销售成本()+"','"+po.get商品报损支出()+"','"+po.get商品报损支出()+"','"+po.get折让()+"');");
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@SuppressWarnings("deprecation")
	private Po add(String name,long value){
		Po result=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s=c.createStatement();
			//如果最新的经营情况不是今天，则新建今天的经营情况
			Date now=new Date();
			Date latest=getLatestDate();
			if(now.getYear()!=latest.getYear()||now.getMonth()!=latest.getMonth()||now.getDate()!=latest.getDate()){
				latest=new Date(now.getYear(),now.getMonth(),now.getDate());
				add经营情况(latest);
			}
			ResultSet rs=s.executeQuery("select "+name+" from "+ab.getName()+"_经营情况 where 日期='"+latest.getTime()+"';");
			if(rs.next()){
			value=value+rs.getLong(1);
			}
			s.executeUpdate("update "+ab.getName()+"_经营情况 set "+name+"='"+value+"' where 日期='"+latest.getTime()+"';");
			result.successed();
		} catch (SQLException e) {
			result.failed(e.getMessage());
			e.printStackTrace();
		}
		return result;		
	}
	
	private Date getLatestDate(){
		Date oldest=new Date(0);
		try {
			Connection c=Database.getConnection();
			Statement s=c.createStatement();
			ResultSet r= s.executeQuery("select max(日期) from "+ab.getName()+"_经营情况");
			if(r.next()){
				oldest.setTime(r.getLong(1));
			}else{
				oldest.setTime(ab.getCreatTime());
			}
			s.close();
		} catch (SQLException e) {
		}
		return oldest;
	}

	

}
